package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.PageModel;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;
import jdk.nashorn.internal.scripts.JD;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据层，连接数据库
 */
public class UserImpl implements UserDao {
    //    list map set区别
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int add(UserModel userModel) {
        String sql = "INSERT INTO `user` " +
                "(`username`, `password`, `phone`," +
                " `sex`, `birthday`, `hobby`, " +
                "`introduce`, `remark`, " +
                "`update_time`, `create_time`, " +
                "`enable`, `email`, " +
                "`height`, `address`) VALUES " +
                "('" + userModel.getUsername() + "', '" + userModel.getPassword() + "', '" + userModel.getPhone() + "'," +
                " '" + userModel.getSex() + "', '" + userModel.getBirthday() + "', '" + userModel.getHobby() + "'," +
                " 'a', 'a'," +
                " now(), now(), " +
                "'0', ''," +
                " '0', '');";
        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "update user set username='" + userModel.getUsername() + "'" +
                ",phone='" + userModel.getPhone() + "' where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    public int updatePassword(UserModel userModel) {
        String sql = "update user set password='" + userModel.getPassword() + "'" +
                " where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    public int updateEnable(UserModel userModel) {
        String sql = "update user set enable=ABS(enable -1)" +
                " where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(int id) {
        String sql = "select * from user where id=" + id;
//        UserModel model = null;
//        ResultSet resultSet = JDBC.query(sql);
//        try {
//            while (resultSet.next()) {
//                model = new UserModel();
//                model.setId(resultSet.getInt("id"));
//                model.setUsername(resultSet.getString("username"));
//                model.setCreatTime(resultSet.getString("create_time"));
//                model.setUpdateTime(resultSet.getString("update_time"));
//                model.setEnable(resultSet.getInt("enable"));
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return model;
        return setList(sql).get(0);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select * from user where  1=1 ";

        if (!"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%'  ";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += " and phone = '%" + userModel.getPhone() + "%'";
        }
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql += " and birthday >='" + userModel.getStart() + "'";
        }
        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday <='" + userModel.getEnd() + "'";
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday between'" + userModel.getStart() + "' and '" + userModel.getEnd() + "'";
        }
        if (!"全部".equals(userModel.getSex())) {
            sql += " and sex='" + userModel.getSex() + "'";
        }
        if (!"".equals(userModel.getHobby()) && userModel.getHobby() != null) {
            String[] hobbys = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                sql += (i == 0 ? "" : "or") + "  find_in_set('" + hobbys[i] + "',hobby)";
            }
            sql += ") ";
        }

//        Integer start = (curPage - 1) * pageSize;
        sql += " order by create_time desc  ";
        sql += " limit  " + (userModel.getPageModel().getCurPage() - 1) * userModel.getPageModel().getPageSize() + "," + userModel.getPageModel().getPageSize() + " ";
        return setList(sql);
    }

    @Override
    public Integer getCount(UserModel userModel) {
        String sql = "select count(*) count from user where 1=1";

        if (!"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%'  ";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += " and phone = '%" + userModel.getPhone() + "%'";
        }
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql += " and birthday >='" + userModel.getStart() + "'";
        }
        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday <='" + userModel.getEnd() + "'";
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday between'" + userModel.getStart() + "' and '" + userModel.getEnd() + "'";
        }
        if (!"全部".equals(userModel.getSex())) {
            sql += " and sex='" + userModel.getSex() + "'";
        }
        if (!"".equals(userModel.getHobby()) && userModel.getHobby() != null) {
            String[] hobbys = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                sql += (i == 0 ? "" : "or") + "  find_in_set('" + hobbys[i] + "',hobby)";
            }
            sql += ") ";
        }
        sql += " order by create_time desc  ";
        ResultSet resultSet = JDBC.query(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    private List<UserModel> setList(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setSex(resultSet.getString("sex"));
                model.setPhone(resultSet.getString("phone"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setCreatTime(resultSet.getString("create_time"));
                model.setUpdateTime(resultSet.getString("update_time"));
                model.setEnable(resultSet.getInt("enable"));
                model.setAddress(resultSet.getString("address"));
                model.setHobby(resultSet.getString("hobby"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (list.size() == 0) {
            list.add(null);
        }
        return list;
    }
}
